//-----------------------------------------------------------------------
// <copyright file="a_UserDAOBase.cs" company="AACECOM">
//     Copyright (c) AACECOM. All rights reserved.
// </copyright>
//-----------------------------------------------------------------------

using System;
using System.Data;
using System.Data.Common;
using System.Collections;
using Microsoft.Practices.EnterpriseLibrary.Data;
using ThoiTrang.Entities;

namespace ThoiTrang.Data
{    
    public abstract class a_UserDAOBase
    {
        #region Common methods
        public virtual a_User Createa_UserFromReader(IDataReader reader)
        {
            a_User item = new a_User();
            try
            {
                if (!reader.IsDBNull(reader.GetOrdinal("UserID"))) item.UserID = (int)reader["UserID"];
                if (!reader.IsDBNull(reader.GetOrdinal("CookieID"))) item.CookieID = (string)reader["CookieID"];
                if (!reader.IsDBNull(reader.GetOrdinal("Login_Email"))) item.Login_Email = (string)reader["Login_Email"];
                if (!reader.IsDBNull(reader.GetOrdinal("EmailConfirmed"))) item.EmailConfirmed = (bool)reader["EmailConfirmed"];
                if (!reader.IsDBNull(reader.GetOrdinal("Password"))) item.Password = (string)reader["Password"];
                if (!reader.IsDBNull(reader.GetOrdinal("Firstname"))) item.Firstname = (string)reader["Firstname"];
                if (!reader.IsDBNull(reader.GetOrdinal("Lastname"))) item.Lastname = (string)reader["Lastname"];
                if (!reader.IsDBNull(reader.GetOrdinal("Title"))) item.Title = (string)reader["Title"];
                if (!reader.IsDBNull(reader.GetOrdinal("Gender"))) item.Gender = (string)reader["Gender"];
                if (!reader.IsDBNull(reader.GetOrdinal("Visits"))) item.Visits = (int)reader["Visits"];
                if (!reader.IsDBNull(reader.GetOrdinal("Hits"))) item.Hits = (int)reader["Hits"];
                if (!reader.IsDBNull(reader.GetOrdinal("LastVisit"))) item.LastVisit = (DateTime)reader["LastVisit"];
                if (!reader.IsDBNull(reader.GetOrdinal("DateOpen"))) item.DateOpen = (DateTime)reader["DateOpen"];
                if (!reader.IsDBNull(reader.GetOrdinal("UserTypeID"))) item.UserTypeID = (int)reader["UserTypeID"];
                if (!reader.IsDBNull(reader.GetOrdinal("IsActive"))) item.IsActive = (bool)reader["IsActive"];
                if (!reader.IsDBNull(reader.GetOrdinal("CarID"))) item.CarID = (int)reader["CarID"];
                if (!reader.IsDBNull(reader.GetOrdinal("IsAccessFeatured"))) item.IsAccessFeatured = (bool)reader["IsAccessFeatured"];
            }
            catch (Exception ex)
            {                
                throw ex;
            }
            return item;
        }
        #endregion
        
        #region Createa_User methods
            
        public virtual void Createa_User(a_User a_User)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("a_Users_ins");
                
                database.AddInParameter(dbCommand, "@CookieID", DbType.AnsiString, a_User.CookieID);
                database.AddInParameter(dbCommand, "@Login_Email", DbType.AnsiString, a_User.Login_Email);
                database.AddInParameter(dbCommand, "@EmailConfirmed", DbType.Boolean, a_User.EmailConfirmed);
                database.AddInParameter(dbCommand, "@Password", DbType.AnsiString, a_User.Password);
                database.AddInParameter(dbCommand, "@Firstname", DbType.String, a_User.Firstname);
                database.AddInParameter(dbCommand, "@Lastname", DbType.String, a_User.Lastname);
                database.AddInParameter(dbCommand, "@Title", DbType.AnsiString, a_User.Title);
                database.AddInParameter(dbCommand, "@Gender", DbType.AnsiStringFixedLength, a_User.Gender);
                database.AddInParameter(dbCommand, "@Visits", DbType.Int32, a_User.Visits);
                database.AddInParameter(dbCommand, "@Hits", DbType.Int32, a_User.Hits);
                database.AddInParameter(dbCommand, "@LastVisit", DbType.DateTime, a_User.LastVisit);
                database.AddInParameter(dbCommand, "@DateOpen", DbType.DateTime, a_User.DateOpen);
                database.AddInParameter(dbCommand, "@UserTypeID", DbType.Int32, a_User.UserTypeID);
                database.AddInParameter(dbCommand, "@IsActive", DbType.Boolean, a_User.IsActive);
                database.AddInParameter(dbCommand, "@CarID", DbType.Int32, a_User.CarID);
                database.AddInParameter(dbCommand, "@IsAccessFeatured", DbType.Boolean, a_User.IsAccessFeatured);
                database.AddOutParameter(dbCommand, "@UserID", DbType.Int32, 0);
                
                database.ExecuteNonQuery(dbCommand);
                a_User.UserID = (int)database.GetParameterValue(dbCommand, "@UserID");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Updatea_User methods
        
        public virtual void Updatea_User(a_User a_User)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("a_Users_upd");            
                
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, a_User.UserID);
                database.AddInParameter(dbCommand, "@CookieID", DbType.AnsiString, a_User.CookieID);
                database.AddInParameter(dbCommand, "@Login_Email", DbType.AnsiString, a_User.Login_Email);
                database.AddInParameter(dbCommand, "@EmailConfirmed", DbType.Boolean, a_User.EmailConfirmed);
                database.AddInParameter(dbCommand, "@Password", DbType.AnsiString, a_User.Password);
                database.AddInParameter(dbCommand, "@Firstname", DbType.String, a_User.Firstname);
                database.AddInParameter(dbCommand, "@Lastname", DbType.String, a_User.Lastname);
                database.AddInParameter(dbCommand, "@Title", DbType.AnsiString, a_User.Title);
                database.AddInParameter(dbCommand, "@Gender", DbType.AnsiStringFixedLength, a_User.Gender);
                database.AddInParameter(dbCommand, "@Visits", DbType.Int32, a_User.Visits);
                database.AddInParameter(dbCommand, "@Hits", DbType.Int32, a_User.Hits);
                database.AddInParameter(dbCommand, "@LastVisit", DbType.DateTime, a_User.LastVisit);
                database.AddInParameter(dbCommand, "@DateOpen", DbType.DateTime, a_User.DateOpen);
                database.AddInParameter(dbCommand, "@UserTypeID", DbType.Int32, a_User.UserTypeID);
                database.AddInParameter(dbCommand, "@IsActive", DbType.Boolean, a_User.IsActive);
                database.AddInParameter(dbCommand, "@CarID", DbType.Int32, a_User.CarID);
                database.AddInParameter(dbCommand, "@IsAccessFeatured", DbType.Boolean, a_User.IsAccessFeatured);
                
                database.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
               throw ex;
            }
        }
        
        #endregion

        #region Deletea_User methods
        public virtual void Deletea_User(int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("a_Users_del");
                
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                
                database.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
               throw ex;
            }
        }

        #endregion

        #region Geta_User methods
        
        public virtual a_User Geta_User(int userID)
        {            
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("a_Users_sel");
                
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                
                a_User a_User =  null;
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        a_User = Createa_UserFromReader(reader);
                        reader.Close();
                    }
                }
                return a_User;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public virtual a_User Geta_UserByLoginEmail(string email)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("a_Users_selByLoginEmail");

                database.AddInParameter(dbCommand, "@Login_Email", DbType.AnsiString, email);

                a_User a_User = null;
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        a_User = Createa_UserFromReader(reader);
                        reader.Close();
                    }
                }
                return a_User;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public virtual a_User Geta_UserBySSID(string ssid)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("a_Users_selBySSID");

                database.AddInParameter(dbCommand, "@ssid", DbType.AnsiString, ssid);

                a_User a_User = null;
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        a_User = Createa_UserFromReader(reader);
                        reader.Close();
                    }
                }
                return a_User;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public virtual a_User Geta_UserByCookieID(string ckid)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("a_Users_selByCookieID");

                database.AddInParameter(dbCommand, "@CookieID", DbType.AnsiString, ckid);

                a_User a_User = null;
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        a_User = Createa_UserFromReader(reader);
                        reader.Close();
                    }
                }
                return a_User;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        #endregion

        #region GetByEmail
        public virtual a_User Geta_UserByEmail(string email)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("a_Users_selByEmail");

                database.AddInParameter(dbCommand, "@Email", DbType.String, email);

                a_User a_User = null;
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        a_User = Createa_UserFromReader(reader);
                        reader.Close();
                    }
                }
                return a_User;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region Geta_UserList methods
        public virtual a_UserCollection Geta_UserList(a_UserColumns orderBy, OrderDirection orderDirection, int page, int pageSize, out int totalRecords)
        {            
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("a_Users_selList");
                
                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy.ToString());
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection.ToString());
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 4);
                
                a_UserCollection a_UserCollection = new a_UserCollection();
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        a_User a_User = Createa_UserFromReader(reader);
                        a_UserCollection.Add(a_User);
                    }
                    reader.Close();
                }
                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");
                return a_UserCollection;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        public virtual a_UserCollection Geta_UserList(a_UserColumns orderBy, OrderDirection orderDirection)
        {            
            int totalRecords = 0;
            return Geta_UserList(orderBy, orderDirection, 0, 0, out totalRecords);
        }

        public virtual a_UserCollection Geta_UserList_typeid(int typeid, a_UserColumns orderBy, OrderDirection orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("a_Users_selList_typeid");
                database.AddInParameter(dbCommand, "@Typeid", DbType.Int32, typeid);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy.ToString());
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection.ToString());
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 4);

                a_UserCollection a_UserCollection = new a_UserCollection();
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        a_User a_User = Createa_UserFromReader(reader);
                        a_UserCollection.Add(a_User);
                    }
                    reader.Close();
                }
                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");
                return a_UserCollection;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public virtual a_UserCollection Geta_UserList_typeid(int typeid, a_UserColumns orderBy, OrderDirection orderDirection)
        {
            int totalRecords = 0;
            return Geta_UserList_typeid(typeid, orderBy, orderDirection, 0, 0, out totalRecords);
        }
        #endregion

        #region Geta_UserList_search methods
        public virtual a_UserCollection Geta_UserList_search(string usertype, bool st, string searchby,string searchtext,DateTime fDate,DateTime eDate, a_UserColumns orderBy, OrderDirection orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("a_Users_selList_search");
                database.AddInParameter(dbCommand, "@usertype", DbType.AnsiString, usertype);
                database.AddInParameter(dbCommand, "@St", DbType.Boolean, st);
                database.AddInParameter(dbCommand, "@SearchBy", DbType.AnsiString, searchby);
                database.AddInParameter(dbCommand, "@SearchText", DbType.AnsiString, searchtext);
                database.AddInParameter(dbCommand, "@fDate", DbType.DateTime, fDate);
                database.AddInParameter(dbCommand, "@eDate", DbType.DateTime, eDate);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy.ToString());
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection.ToString());
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand,"@TotalRecords", DbType.Int32, 4);

                a_UserCollection a_UserCollection = new a_UserCollection();
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        a_User a_User = Createa_UserFromReader(reader);
                        a_UserCollection.Add(a_User);
                    }
                    reader.Close();
                }
                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");
                return a_UserCollection;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public virtual a_UserCollection Geta_UserList_search(string usertype, bool st, string searchby, string searchtext, DateTime fDate, DateTime eDate, a_UserColumns orderBy, OrderDirection orderDirection)
        {
            int totalRecords = 0;
            return Geta_UserList_search(usertype, st, searchby,searchtext,fDate,eDate, orderBy, orderDirection, 0, 0, out totalRecords);
        }

        #endregion

        public virtual a_User Geta_User(string sessionId)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("a_Users_sel");

                database.AddInParameter(dbCommand, "@SessionId", DbType.String, sessionId);

                a_User a_User = null;
                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        a_User = Createa_UserFromReader(reader);
                        reader.Close();
                    }
                }
                return a_User;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}